﻿using DevExpress.Data.Filtering;
using SMALLERP;
using SMALLERP.DataClass;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using XJLSC.PM;

namespace XJLSC.FM
{
    public partial class WLQG : Form
    {
        private readonly DataBase db = new DataBase();
        private SqlDataReader sdr1;
        public WLQG()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string strSql;
            String mos = "";
            int[] rows = this.gridView2.GetSelectedRows();
            foreach (int row in rows)
            {
                String mo = this.gridView2.GetRowCellValue(row, "工单号").ToString();
                if (mos == "")
                {
                    mos = mo;
                }
                else
                {
                    mos += "','" + mo;
                }
            }

            strSql = "SELECT (CASE WHEN ISNULL(I.SUPPLIER_FICATEGORY_NAME,'') LIKE '%钣金%' THEN D.OPERATION_NAME ELSE '' END) 工艺,C.ITEM_CODE 品号,C.ITEM_NAME 品名,C.ITEM_SPECIFICATION 规格,SUM(CAST(A.REQUIRED_QTY AS REAL)) 需领用量,ISNULL(I.SUPPLIER_FICATEGORY_NAME,'') 采购分类,MAX(A.PLAN_ISSUE_DATE) 需求日期,CAST(ISNULL((SELECT KCSL FROM V_WLPH WHERE TD004=C.ITEM_CODE ),0) AS REAL) 库存数量,CAST(ISNULL((SELECT AQKC FROM V_WLPH WHERE TD004=C.ITEM_CODE ),0) AS REAL) 安全库存,CAST(ISNULL((SELECT YJQG FROM V_WLPH WHERE TD004=C.ITEM_CODE ),0) AS REAL) 预计请购,CAST(ISNULL((SELECT YJJH FROM V_WLPH WHERE TD004=C.ITEM_CODE ),0) AS REAL) 预计进货,CAST(ISNULL((SELECT YJLL FROM V_WLPH WHERE TD004=C.ITEM_CODE ),0) AS REAL) 预计领料,CAST(ISNULL((SELECT DDSL FROM V_WLPH WHERE TD004=C.ITEM_CODE ),0) AS REAL) 预计销货 FROM MO_D A LEFT JOIN MO B ON A.MO_ID=B.MO_ID LEFT JOIN ITEM C ON C.ITEM_BUSINESS_ID=A.ITEM_ID LEFT JOIN OPERATION D ON D.OPERATION_ID=A.OPERATION_ID LEFT JOIN ITEM_PLANT E ON E.Owner_Org_ROid=B.Owner_Org_ROid AND E.ITEM_ID=A.ITEM_ID LEFT JOIN ITEM_PURCHASE F ON E.ITEM_ID=F.ITEM_ID LEFT JOIN ITEM_SUPPLIER G ON G.ITEM_BUSINESS_ID=F.ITEM_BUSINESS_ID LEFT JOIN SUPPLIER H ON H.SUPPLIER_BUSINESS_ID=G.SUPPLIER_ID LEFT JOIN SUPPLIER_FI J ON J.SUPPLIER_ID=H.SUPPLIER_BUSINESS_ID LEFT JOIN SUPPLIER_FICATEGORY I ON I.SUPPLIER_FICATEGORY_ID=J.SUPPLIER_FICATEGORY_ID WHERE B.DOC_NO IN ('" + mos + "') AND E.ITEM_PROPERTY='P' GROUP BY (CASE WHEN ISNULL(I.SUPPLIER_FICATEGORY_NAME,'') LIKE '%钣金%' THEN D.OPERATION_NAME ELSE '' END),C.ITEM_CODE,C.ITEM_NAME,C.ITEM_CODE,C.ITEM_SPECIFICATION,ISNULL(I.SUPPLIER_FICATEGORY_NAME,'')  ORDER BY C.ITEM_CODE";
            //textBox1.Text = strSql;
            try
            {
                DataTable dt;
                dt = db.GetDataSet1(strSql, "PURTD").Tables["PURTD"];
                gridControl1.DataSource = dt;
                this.gridView1.OptionsView.ShowFilterPanelMode = DevExpress.XtraGrid.Views.Base.ShowFilterPanelMode.Never;
                this.gridView1.ActiveFilterCriteria = BulidFilterCriteria();
                this.gridView1.RefreshData();
                //gridView1.ApplyFindFilter("[请购数量]>0");               
            }
            catch
            {
            }
        }

        private GroupOperator BulidFilterCriteria()
        {
            CriteriaOperatorCollection filterCollection = new CriteriaOperatorCollection();
            //四列中都参与数据过滤
            filterCollection.Add(CriteriaOperator.Parse(string.Format("[请购数量]>0", 0)));
            //filterCollection.Add(CriteriaOperator.Parse(string.Format("MTNAME LIKE '%{0}%'", filter_text.Text)));
            //filterCollection.Add(CriteriaOperator.Parse(string.Format("PYCODE LIKE '%{0}%'", filter_text.Text)));
            //filterCollection.Add(CriteriaOperator.Parse(string.Format("WBCODE LIKE '%{0}%'", filter_text.Text)));
            return new GroupOperator(GroupOperatorType.Or, filterCollection);
        }

        private void gridView1_SelectionChanged(object sender, DevExpress.Data.SelectionChangedEventArgs e)
        {
     
        }

        private void gridView1_FocusedRowChanged(object sender, DevExpress.XtraGrid.Views.Base.FocusedRowChangedEventArgs e)
        {

            //string strSql;
            //String zjph = gridView1.GetFocusedRowCellValue("品号").ToString();
            //String gcph = gridView1.GetFocusedRowCellValue("工厂ID").ToString();
            //strSql = "SELECT R_YJ_ID AS ID,R_ZJ_ID AS ParentID,B.ITEM_CODE 品号,B.ITEM_NAME 品名,B.ITEM_SPECIFICATION 规格,R_Level,[Path] path FROM d_Bom1('" + zjph + "','" + gcph + "') Z LEFT JOIN ITEM A ON Z.R_ZJPH=A.ITEM_CODE LEFT JOIN ITEM B ON Z.R_YJPH=B.ITEM_CODE  WHERE R_Level<=" + textBox2.Text + " ORDER BY R_Level,[Path]";
            ////textBox1.Text = strSql;
            //try
            //{
            //    DataTable dataTable;
            //    dataTable = db.GetDataSet1(strSql, "PURTD").Tables["PURTD"];
            //    gridControl2.DataSource = dataTable;
            //    treeList1.DataSource = dataTable;
            //    treeList1.KeyFieldName = "ID";
            //    treeList1.ParentFieldName = "ParentID";
            //    //treeList1.ExpandAll();
            //    treeList1.Refresh();
            //}
            //catch
            //{

            }

        private void WLQG_Load(object sender, EventArgs e)
        {
            tabPage2.Parent = null;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            SaveFileDialog fileDialog = new SaveFileDialog();
            fileDialog.Title = "导出Excel";
            fileDialog.Filter = "Excel文件(*.xls)|*.xls";
            DialogResult dialogResult = fileDialog.ShowDialog(this);
            if (dialogResult == DialogResult.OK)
            {
                DevExpress.XtraPrinting.XlsExportOptions options = new DevExpress.XtraPrinting.XlsExportOptions();
                gridControl1.ExportToXls(fileDialog.FileName);
                DevExpress.XtraEditors.XtraMessageBox.Show("保存成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //先查找请购单号
            String code = "";
           String strSql = "SELECT QGDH FROM [dbo].[fn_QGDH]('3101',GETDATE())";
            try
            {
                sdr1 = db.GetDataReader1(strSql);
                while (sdr1.Read())
                {
                    code = sdr1["QGDH"].ToString();
                }
            }
            catch (Exception ex)
            {
               MessageBox.Show(ex.Message, "软件提示");
                //throw ex;
            }
            finally
            {
              sdr1.Close();
            }
            //插入单头
            strSql = "INSERT INTO REQUISITION(DOC_NO,DOC_DATE,DOC_ID,Owner_Dept,Owner_Emp,REQUISITION_ID,REQUISITION_DATE,REQUISITION_TYPE,REMARK,CATEGORY,UDF001,UDF002,UDF003,UDF011,UDF012,UDF013,UDF021,UDF022,UDF023,UDF024,UDF025,UDF026,UDF041,UDF042,UDF051,UDF052,UDF053,UDF054,PrintCount,EFNETStatus,EFNETAction,EFNETDOCCategory,EFNETDOCNumber,CreateDate,LastModifiedDate,ModifiedDate,CreateBy,LastModifiedBy,ModifiedBy,Attachments,ProcessInstanceId,ApproveStatus,ApproveDate,ApproveBy,Owner_Org_RTK,Owner_Org_ROid)";
            strSql += "SELECT '" + code + "',CONVERT(CHAR(20), GETDATE(), 120),DOC_ID,'00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000',NEWID(),CONVERT(CHAR(20), GETDATE(), 120),'9','','31',0,0,0,0,0,0,'','','','','','','1900-01-01 00:00:00.0000000','1900-01-01 00:00:00.0000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000',0,0,0,'','',CONVERT(CHAR(20), GETDATE(), 120),CONVERT(CHAR(20), GETDATE(), 120),CONVERT(CHAR(20), GETDATE(), 120),'CDCAB790-A674-8BD3-F817-C04C90B7CACD','CDCAB790-A674-8BD3-F817-C04C90B7CACD','CDCAB790-A674-8BD3-F817-C04C90B7CACD','','00000000-0000-0000-0000-000000000000','N',CONVERT(CHAR(20), GETDATE(), 120),'00000000-0000-0000-0000-000000000000','PLANT','90DDCEE1-93C2-432E-F0A5-1712B6B24FB6' FROM DOC WHERE DOC_CODE = '3101'";
            try
            {
                if (db.ExecDataBySql1(strSql) > 0)
                {
                }
                else
                {

                }
            }
            catch
            {

            }


            //插入单身

            int[] rows = this.gridView1.GetSelectedRows();
            int i = 0;
            foreach (int row in rows)
            {
                i++;
                String qty = this.gridView1.GetRowCellValue(row, "请购数量").ToString();
                String qgph = this.gridView1.GetRowCellValue(row, "品号").ToString();
                String xqrq = this.gridView1.GetRowCellValue(row, "需求日期").ToString();
                String qgbz = this.gridView1.GetRowCellValue(row, "需求日期").ToString();
                String zjxx = this.gridView1.GetRowCellValue(row, "主件品号").ToString()+"("+ this.gridView1.GetRowCellValue(row, "主件品名").ToString()+")";
                strSql = "INSERT INTO REQUISITION_D(SequenceNumber,REQUISITION_D_ID,ITEM_DESCRIPTION,ITEM_SPECIFICATION,INVENTORY_QTY,SECOND_QTY,REQUISITION_QTY,REQUIREMENT_DATE,URGENCY,MANUFACTURER,REMARK,PURCHASED_QTY,APPLY_QTY,DISPATCH_STATUS,PACKING_QTY1,PACKING_QTY2,PACKING_QTY3,PACKING_QTY4,PACKING_QTY,ITEM_FEATURE_ID,ITEM_ID,REQUISITION_UNIT_ID,SUPPLIER_ID,ITEM_CERTIFICATION_D_ID,SUPPLY_CENTER_ID,WAREHOUSE_ID,PACKING_MODE_ID,PACKING1_UNIT_ID,PACKING2_UNIT_ID,PACKING3_UNIT_ID,PACKING4_UNIT_ID,PROJECT_ID,DEMAND_NO,SUPPLY_DATE,PLAN_ARRIVAL_DATE,PRICE,AMOUNT,BUDGET_ADMIN_UNIT_ID,BUDGET_GROUP_ID,BUDGET_ITEM_ID,PRE_BUDGET_ID,PRE_BUDGET_D_ID,BUDGET_ID,BUDGET_D_ID,APS_DOC_NO,UDF001,UDF002,UDF003,UDF011,UDF012,UDF013,UDF021,UDF022,UDF023,UDF024,UDF025,UDF026,UDF041,UDF042,UDF051,UDF052,UDF053,UDF054,CreateDate,LastModifiedDate,ModifiedDate,CreateBy,LastModifiedBy,ModifiedBy,ApproveStatus,ApproveDate,ApproveBy,SOURCE_ID_RTK,SOURCE_ID_ROid,REFERENCE_SOURCE_ID_RTK,REFERENCE_SOURCE_ID_ROid,REQUISITION_ID)";
                strSql += "SELECT '" + i.ToString() + "',NEWID(),ITEM_NAME,ITEM_SPECIFICATION," + qty + ",0," + qty + ",'" + xqrq + "',0,'" + qgbz + "','',0,0,'1',0,0,0,0,'','00000000-0000-0000-0000-000000000000',ITEM.ITEM_BUSINESS_ID,ITEM.STOCK_UNIT_ID,'00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','C9AA0C13-9ECE-47C2-7271-1712B78C6B2B',INBOUND_WAREHOUSE_ID,'00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','','1900-01-01 00:00:00.0000000','1900-01-01 00:00:00.0000000',0,0,'00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','',ISNULL(V_ZXCG.PRICE,0),0,ISNULL(V_ZXCG.PRICE,0),ISNULL((SELECT SUM(XQL)  FROM WLXQ WHERE ID=ITEM.ITEM_CODE AND LB='需求'),0),ISNULL(V_ZXCG.BUSINESS_QTY,0),0,'','','" + zjxx + "','','','',ISNULL(V_ZXCG.CreateDate,GETDATE()),A.UDF042,A.UDF051,A.UDF052,A.UDF053,A.UDF054,A.CreateDate,A.LastModifiedDate,A.ModifiedDate,A.CreateBy,A.LastModifiedBy,A.ModifiedBy,'N',A.ApproveDate,A.ApproveBy,'OTHER','00000000-0000-0000-0000-000000000000','OTHER','00000000-0000-0000-0000-000000000000',A.REQUISITION_ID FROM REQUISITION A LEFT JOIN ITEM_PLANT ON A.Owner_Org_ROid=ITEM_PLANT.Owner_Org_ROid LEFT JOIN ITEM  ON ITEM.ITEM_BUSINESS_ID = ITEM_PLANT.ITEM_ID LEFT JOIN V_ZXCG ON V_ZXCG.ITEM_ID=ITEM.ITEM_BUSINESS_ID WHERE DOC_NO = '" + code + "' AND ITEM_CODE = '" + qgph + "'";
                //textBox1.Text = strSql;
                try
                {
                    if (db.ExecDataBySql1(strSql) > 0)
                    {
                    }
                    else
                    {

                    }
                }
                catch
                {

                }
            }

            //最后插入需求档
            strSql = "INSERT INTO REQUISITION_SD(SequenceNumber,REQUISITION_SD_ID,DEMAND_NO,DEMAND_QTY,DEMAND_DATE,PURCHASED_QTY,ARRIVED_QTY,RECEIPTED_QTY,PURCHASE_SEQUENCE,REMARK,REQ_DISPATCHED_ID,PLAN_SHIP_DATE,LOCKED_FLAG,INVENTORY_QTY,SOURCE_DEMAND_QTY,REQUISITION_D_DATE,PUR_INVENTORY_QTY,APS_DOC_NO,UDF001,UDF002,UDF003,UDF011,UDF012,UDF013,UDF021,UDF022,UDF023,UDF024,UDF025,UDF026,UDF041,UDF042,UDF051,UDF052,UDF053,UDF054,CreateDate,LastModifiedDate,ModifiedDate,CreateBy,LastModifiedBy,ModifiedBy,ApproveStatus,ApproveDate,ApproveBy,SOURCE_ID_RTK,SOURCE_ID_ROid,REFERENCE_SOURCE_ID_RTK,REFERENCE_SOURCE_ID_ROid,REQUISITION_D_ID)";
            strSql += " SELECT SequenceNumber,NEWID(),DEMAND_NO,REQUISITION_QTY,REQUIREMENT_DATE,0,0,0,SequenceNumber,'DPP运算产生','00000000-0000-0000-0000-000000000000',REQUIREMENT_DATE,0,REQUISITION_QTY,0,REQUISITION_D.CreateDate,0,'',0,0,0,0,0,0,'','','','','','','1900-01-01 00:00:00.0000000','1900-01-01 00:00:00.0000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000',REQUISITION_D.CreateDate,REQUISITION_D.LastModifiedDate,REQUISITION_D.ModifiedDate,REQUISITION_D.CreateBy,REQUISITION_D.LastModifiedBy,REQUISITION_D.ModifiedBy,'N',GETDATE(),'00000000-0000-0000-0000-000000000000','OTHER','00000000-0000-0000-0000-000000000000','OTHER','00000000-0000-0000-0000-000000000000',REQUISITION_D_ID FROM REQUISITION_D WHERE NOT EXISTS(SELECT * FROM REQUISITION_SD WHERE REQUISITION_SD.REQUISITION_D_ID=REQUISITION_D.REQUISITION_D_ID) AND EXISTS(SELECT * FROM REQUISITION WHERE REQUISITION.REQUISITION_ID=REQUISITION_D.REQUISITION_ID AND REQUISITION.DOC_NO='" + code + "')";
            //textBox1.Text = strSql;
            try
            {
                if (db.ExecDataBySql1(strSql) > 0)
                {
                }
                else
                {
                }
            }
            catch
            {
            }

            MessageBox.Show("请购单产生成功！" + code, "软件提示");

        }

        private void button4_Click(object sender, EventArgs e)
        {
            string strSql;
            if (checkBox1.CheckState == CheckState.Unchecked)
            {
                strSql = "SELECT B.DOC_NO 工单号,C.ITEM_CODE 产品品号,C.ITEM_NAME 产品品名,C.ITEM_SPECIFICATION 规格,ISNULL(D.PROJECT_CODE,'') 项目号,B.REMARK 备注 FROM MO B LEFT JOIN ITEM C ON C.ITEM_BUSINESS_ID=B.ITEM_ID LEFT JOIN PROJECT D ON D.PROJECT_ID=B.PROJECT_ID WHERE B.[STATUS]<>'Y' AND B.[STATUS]<>'y' AND ISNULL(D.PROJECT_CODE,'') LIKE '%" + textBox1.Text + "%' AND B.DOC_DATE>='" + dateTimePicker1.Value.ToShortDateString() + "' AND B.DOC_DATE<='" + dateTimePicker2.Value.ToShortDateString() + "'";               
                try
                {
                    DataTable dt;
                    dt = db.GetDataSet1(strSql, "MOCTA").Tables["MOCTA"];
                    gridControl2.DataSource = dt;
                    //gridView1.ApplyFindFilter("[请购数量]>'0'");               
                }
                catch
                {

                }
            }
            else
            {
                if (checkBox2.CheckState == CheckState.Unchecked)
                {
                    strSql = "SELECT ISNULL(I.SUPPLIER_FICATEGORY_NAME,'') 采购分类,D.ITEM_BUSINESS_ID AS ITEM_ID,CONVERT(CHAR(8),DATEADD(DAY,MB036*(-1),MAX(XQRQ)),112) 请购日期,MAX(XQRQ) 需求日期,D.ITEM_CODE 品号,D.ITEM_NAME 品名,D.ITEM_SPECIFICATION 规格,'' 主件品号,'' 主件品名,CAST(SUM(Z.XQL) AS REAL) 需领用量,CAST(ISNULL((SELECT KCSL FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL) 库存数量,CAST(ISNULL((SELECT AQKC FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL) 安全库存,CAST(ISNULL((SELECT YJQG FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL) 预计请购,CAST(ISNULL((SELECT YJJH+CGDY+CGDRK FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL) 预计进货,CEILING(CAST(ISNULL((SELECT YJLL FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL)) 预计领料,CAST(ISNULL((SELECT DDSL FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL) 预计销货,CEILING((CASE WHEN CAST(SUM(Z.XQL) AS REAL)-CAST(ISNULL((SELECT KCSL FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL)+CAST(ISNULL((SELECT AQKC FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL)-CAST(ISNULL((SELECT YJJH+CGDY+CGDRK FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL)-CAST(ISNULL((SELECT YJQG FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL)<0 THEN 0 ELSE CAST(SUM(Z.XQL) AS REAL)-CAST(ISNULL((SELECT KCSL FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL)+CAST(ISNULL((SELECT AQKC FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL)-CAST(ISNULL((SELECT YJJH+CGDY FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL)-CAST(ISNULL((SELECT YJQG FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL) END)/(CASE WHEN F.ORDER_MULTIPLE=0 THEN 1 ELSE F.ORDER_MULTIPLE END))*(CASE WHEN F.ORDER_MULTIPLE=0 THEN 1 ELSE F.ORDER_MULTIPLE END) 请购数量  FROM WLXQ Z LEFT JOIN ITEM D ON D.ITEM_CODE=Z.ID LEFT JOIN ITEM_PLANT ON D.ITEM_BUSINESS_ID=ITEM_PLANT.ITEM_ID LEFT JOIN ITEM_WAREHOUSE E ON E.ITEM_ID=ITEM_PLANT.ITEM_ID AND E.WAREHOUSE_ID=ITEM_PLANT.INBOUND_WAREHOUSE_ID  LEFT JOIN OPERATION ON OPERATION.OPERATION_CODE=Z.GY LEFT JOIN ITEM_PURCHASE F ON D.ITEM_BUSINESS_ID=F.ITEM_ID AND F.Owner_Org_ROid='C9AA0C13-9ECE-47C2-7271-1712B78C6B2B' LEFT JOIN ITEM_SUPPLIER G ON G.ITEM_BUSINESS_ID=F.ITEM_BUSINESS_ID LEFT JOIN SUPPLIER H ON H.SUPPLIER_BUSINESS_ID=G.SUPPLIER_ID LEFT JOIN SUPPLIER_FI J ON J.SUPPLIER_ID=H.SUPPLIER_BUSINESS_ID LEFT JOIN SUPPLIER_FICATEGORY I ON I.SUPPLIER_FICATEGORY_ID=J.SUPPLIER_FICATEGORY_ID LEFT JOIN INVMB ON D.ITEM_CODE=MB001 WHERE Z.LB='需求' AND ISNULL(G.MAIN,1)=1 AND ITEM_PLANT.ITEM_PROPERTY='P' AND CONVERT(CHAR(8),DATEADD(DAY,MB036*(-1),XQRQ),112)<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker2.Value.ToShortDateString() + "'),112) GROUP BY Z.ID,D.ITEM_BUSINESS_ID,D.ITEM_CODE,D.ITEM_NAME,D.ITEM_SPECIFICATION,ISNULL(I.SUPPLIER_FICATEGORY_NAME,''),MB036,F.ORDER_MULTIPLE";
                }
                else
                {
                    strSql = "SELECT ISNULL(I.SUPPLIER_FICATEGORY_NAME,'') 采购分类,D.ITEM_BUSINESS_ID AS ITEM_ID,CONVERT(CHAR(8),DATEADD(DAY,MB036*(-1),MAX(XQRQ)),112) 请购日期,MAX(XQRQ) 需求日期,D.ITEM_CODE 品号,D.ITEM_NAME 品名,D.ITEM_SPECIFICATION 规格,RTRIM(Z.ZJPH) 主件品号,K.ITEM_NAME 主件品名,CAST(SUM(Z.XQL) AS REAL) 需领用量,CAST(ISNULL((SELECT KCSL FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL) 库存数量,CAST(ISNULL((SELECT AQKC FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL) 安全库存,CAST(ISNULL((SELECT YJQG FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL) 预计请购,CAST(ISNULL((SELECT YJJH+CGDY+CGDRK  FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL) 预计进货,CEILING(CAST(ISNULL((SELECT YJLL FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL)) 预计领料,CAST(ISNULL((SELECT DDSL FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL) 预计销货,CEILING((CASE WHEN CAST(SUM(Z.XQL) AS REAL)-CAST(ISNULL((SELECT KCSL FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL)+CAST(ISNULL((SELECT AQKC FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL)-CAST(ISNULL((SELECT YJJH+CGDY+CGDRK  FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL)-CAST(ISNULL((SELECT YJQG FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL)<0 THEN 0 ELSE CAST(SUM(Z.XQL) AS REAL)-CAST(ISNULL((SELECT KCSL FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL)+CAST(ISNULL((SELECT AQKC FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL)-CAST(ISNULL((SELECT YJJH+CGDY FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL)-CAST(ISNULL((SELECT YJQG FROM V_WLPH WHERE TD004=Z.ID),0) AS REAL) END)/(CASE WHEN F.ORDER_MULTIPLE=0 THEN 1 ELSE F.ORDER_MULTIPLE END))*(CASE WHEN F.ORDER_MULTIPLE=0 THEN 1 ELSE F.ORDER_MULTIPLE END) 请购数量  FROM WLXQ Z LEFT JOIN ITEM D ON D.ITEM_CODE=Z.ID LEFT JOIN ITEM_PLANT ON D.ITEM_BUSINESS_ID=ITEM_PLANT.ITEM_ID LEFT JOIN ITEM_WAREHOUSE E ON E.ITEM_ID=ITEM_PLANT.ITEM_ID AND E.WAREHOUSE_ID=ITEM_PLANT.INBOUND_WAREHOUSE_ID  LEFT JOIN OPERATION ON OPERATION.OPERATION_CODE=Z.GY LEFT JOIN ITEM_PURCHASE F ON D.ITEM_BUSINESS_ID=F.ITEM_ID AND F.Owner_Org_ROid='C9AA0C13-9ECE-47C2-7271-1712B78C6B2B' LEFT JOIN ITEM_SUPPLIER G ON G.ITEM_BUSINESS_ID=F.ITEM_BUSINESS_ID LEFT JOIN SUPPLIER H ON H.SUPPLIER_BUSINESS_ID=G.SUPPLIER_ID LEFT JOIN SUPPLIER_FI J ON J.SUPPLIER_ID=H.SUPPLIER_BUSINESS_ID LEFT JOIN SUPPLIER_FICATEGORY I ON I.SUPPLIER_FICATEGORY_ID=J.SUPPLIER_FICATEGORY_ID LEFT JOIN INVMB ON D.ITEM_CODE=MB001 LEFT JOIN ITEM K ON K.ITEM_CODE=Z.ZJPH WHERE Z.LB='需求' AND ISNULL(G.MAIN,1)=1 AND ITEM_PLANT.ITEM_PROPERTY='P' AND CONVERT(CHAR(8),DATEADD(DAY,MB036*(-1),XQRQ),112)<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker2.Value.ToShortDateString() + "'),112) AND (Z.GZZX='1007' OR Z.GZZX='1008') GROUP BY Z.ID,D.ITEM_BUSINESS_ID,D.ITEM_CODE,D.ITEM_NAME,D.ITEM_SPECIFICATION,ISNULL(I.SUPPLIER_FICATEGORY_NAME,''),MB036,F.ORDER_MULTIPLE,RTRIM(Z.ZJPH),K.ITEM_NAME";
                }
                //textBox1.Text = strSql;
                try
                {
                    DataTable dt;
                    dt = db.GetDataSet1(strSql, "MOCTA").Tables["MOCTA"];
                    gridControl1.DataSource = dt;
                    //gridView1.ApplyFindFilter("[请购数量]>'0'");               
                }
                catch
                {

                }

            }
        }

        private void 查看明细ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            string x1 = "";
            string x2 = "";
            double x3 = 0.00;
            string x4 = "";
            string x5 = "";
            x1 = gridView1.GetFocusedRowCellValue("品号").ToString();
            x2 = gridView1.FocusedColumn.Caption.ToString();
            x3 = Convert.ToDouble(gridView1.GetFocusedValue());

            if (gridView1.FocusedRowHandle > -1 && gridView1.FocusedColumn.ColumnHandle == this.gridView1.Columns["需领用量"].ColumnHandle)
            {
                x4 = "查看需领用量";
                x5 = dateTimePicker2.Value.ToShortDateString();

            }
            else if (gridView1.FocusedRowHandle > -1 && gridView1.FocusedColumn.ColumnHandle == this.gridView1.Columns["库存数量"].ColumnHandle)
            {
                x4 = "查看库存";

            }
            //else if (gridView1.FocusedRowHandle > -1 && gridView1.FocusedColumn.ColumnHandle == this.gridView1.Columns["预计生产"].ColumnHandle)
            //{
            //    x4 = "查看工单";

            //}
            else if (gridView1.FocusedRowHandle > -1 && gridView1.FocusedColumn.ColumnHandle == this.gridView1.Columns["预计领料"].ColumnHandle)
            {
                x4 = "查看工单单身";

            }
            else if (gridView1.FocusedRowHandle > -1 && gridView1.FocusedColumn.ColumnHandle == this.gridView1.Columns["预计进货"].ColumnHandle)
            {
                x4 = "查看采购订单";

            }
            else if (gridView1.FocusedRowHandle > -1 && gridView1.FocusedColumn.ColumnHandle == this.gridView1.Columns["预计请购"].ColumnHandle)
            {
                x4 = "查看请购单";

            }
            else
            {
                x4 = "其他";
                MessageBox.Show("本列无明细数据", "软件提示");
            }

            if (x4 != "其他")
            {
                MXCX xx = new MXCX(x1, x2, x3, x4, x5);
                CHAXUN f = new CHAXUN();
                f.cdgzzx += new CHAXUN.Cdgzzx(textBox1_TextChanged);
                f.Show(this);
            }
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {

        }

        ////新建数据源
        //DataTable dataTable = new DataTable();
        ////添加数据列
        //dataTable.Columns.Add("keyId");
        //dataTable.Columns.Add("parentId");
        //dataTable.Columns.Add("name");
        //dataTable.Columns.Add("age", typeof(double));
        //dataTable.Columns.Add("cellphone");
        //dataTable.Columns.Add("information");
        //dataTable.Columns["name"].Caption = "名称";
        //dataTable.Columns["age"].Caption = "年龄";
        //dataTable.Columns["cellphone"].Caption = "手机号";
        //dataTable.Columns["information"].Caption = "信息";
        //添加数据行
        //dataTable.Rows.Add("2DE73424-29F1-4040-C222-1936242BEC6E", "00000000-0000-0000-0000-000000000000", "A区");
        //dataTable.Rows.Add("8894B001-DB23-4BE0-4154-193625C7671A", "2DE73424-29F1-4040-C222-1936242BEC6E", "AGA120AEA06A-024", 18);
        //dataTable.Rows.Add("DBEA2178-8637-4997-C28F-19349BF5E11E", "2DE73424-29F1-4040-C222-1936242BEC6E", "汽油机组件", 20);
        //dataTable.Rows.Add("BB2", "BB0", "B区");
        //dataTable.Rows.Add("BB21", "BB2", "a", 23);
        //dataTable.Rows.Add("BB221", "BB21", "b", 25);
        //dataTable.Rows.Add("BB2211", "BB221", "b", 25);
        //dataTable.Rows.Add("BB22", "BB2", "b", 25);
        //dataTable.Rows.Add("BB23", "BB2", "c", 24);
        //dataTable.Rows.Add("BB231", "BB23", "c", 24);
        //dataTable.Rows.Add("BB2311", "BB231", "c", 24);
        //dataTable.Rows.Add("BB21", "BB231", "a", 23);

        //    //绑定数据源
        //    gridControl2.DataSource = dataTable;
        //treeList1.DataSource = dataTable;
        ////keyId表示节点本身的id
        //treeList1.KeyFieldName = "keyId";
        ////parentId表示父节点的id
        //treeList1.ParentFieldName = "parentId";


    }

        //private void treeList1_AfterCheckNode(object sender, DevExpress.XtraTreeList.NodeEventArgs e)
        //{
        //    if (e.Node == null) return;
        //                 System.Data.DataRowView rov = treeList1.GetDataRecordByNode(e.Node) as System.Data.DataRowView;
        //                 if (e.Node.CheckState == CheckState.Indeterminate)
        //                     {
        //                         e.Node.CheckState = CheckState.Checked;
        //                     }
        //                if (e.Node.CheckState == CheckState.Checked)
        //                     {
        //                         if (e.Node.HasChildren)
        //                            {
        //                                 CheckNode(e.Node);
        //                             }
        //                        ValidParentIsChecked(e.Node);
        //                     }
        //                 else if (e.Node.CheckState == CheckState.Unchecked)
        //                     {
        //                         if (e.Node.HasChildren)
        //                            {
        //                                ClearNode(e.Node);
        //                             }
        //                         //从根节点往下 查询 是否有打钩的子节点，如果有那么 父节点的 半选状态不变否则变为 不选择状态
        //         ValidParentNodeIsCanleSel(e.Node);
        //                     }
        //}

        //private void CheckNode(DevExpress.XtraTreeList.Nodes.TreeListNode node)
        //  {
        //      foreach (DevExpress.XtraTreeList.Nodes.TreeListNode cnode in node.Nodes)
        //      {
        //         if (cnode != null)
        //          {
        //             cnode.CheckState = CheckState.Checked;
        //           }
        //         if (cnode.HasChildren)
        //         {
        //             CheckNode(cnode);
        //         }
        //     }
        // }

        //private void ClearNode(DevExpress.XtraTreeList.Nodes.TreeListNode node)
        //{
        //    foreach (DevExpress.XtraTreeList.Nodes.TreeListNode cnode in node.Nodes)
        //    {
        //        if (cnode != null)
        //        {
        //            cnode.CheckState = CheckState.Unchecked;
        //        }
        //        if (cnode.HasChildren)
        //        {
        //            CheckNode(cnode);
        //        }
        //    }
        //}

        //private void ValidParentIsChecked(DevExpress.XtraTreeList.Nodes.TreeListNode node)
        //{
        //    if (node.ParentNode != null)
        //    {
        //        if (node.ParentNode.CheckState != CheckState.Checked)
        //            node.ParentNode.CheckState = CheckState.Indeterminate;
        //        ValidParentIsChecked(node.ParentNode);
        //    }
        //}

        //private void ValidParentNodeIsCanleSel(DevExpress.XtraTreeList.Nodes.TreeListNode node)
        //{
        //    bool isSel = false;
        //    if (node.ParentNode != null)
        //    {
        //        if (node.ParentNode.CheckState == CheckState.Indeterminate)
        //        {//如果父节点的 状态为 半选 状态 这 更具父节点 判断子节点是否打钩
        //            isSel = ValidIsHasCheckChildNode(node.ParentNode);
        //            if (isSel == false)
        //            {//如果所有的 子节点 都没有 “选中”那么 父节点的状态 变为 非选中状态
        //                node.ParentNode.CheckState = CheckState.Unchecked;
        //            }
        //        }
        //        ValidParentNodeIsCanleSel(node.ParentNode);
        //    }
        //}

        //private bool ValidIsHasCheckChildNode(DevExpress.XtraTreeList.Nodes.TreeListNode node)
        //{
        //    bool isCheck = false;
        //    foreach (DevExpress.XtraTreeList.Nodes.TreeListNode cnode in node.Nodes)
        //    {
        //        if (cnode != null)
        //        {
        //            if (cnode.CheckState == CheckState.Checked)
        //            {
        //                isCheck = true;
        //                return isCheck;
        //            }
        //        }
        //        if (cnode.HasChildren)
        //        {
        //            isCheck = ValidIsHasCheckChildNode(cnode);
        //            if (isCheck == true)
        //            {
        //                return isCheck;
        //            }
        //        }
        //    }
        //    return isCheck;
        //}

        //private void treeList1_BeforeCheckNode(object sender, DevExpress.XtraTreeList.CheckNodeEventArgs e)
        //{
         
        //}

        //private void button3_Click(object sender, EventArgs e)
        //{
        //    SaveFileDialog fileDialog = new SaveFileDialog();
        //    fileDialog.Title = "导出Excel";
        //    fileDialog.Filter = "Excel文件(*.xls)|*.xls";
        //    DialogResult dialogResult = fileDialog.ShowDialog(this);
        //    if (dialogResult == DialogResult.OK)
        //    {
        //        DevExpress.XtraPrinting.XlsExportOptions options = new DevExpress.XtraPrinting.XlsExportOptions();
        //        gridControl1.ExportToXls(fileDialog.FileName);
        //        DevExpress.XtraEditors.XtraMessageBox.Show("保存成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        //    }
        //}

        //private void button2_Click(object sender, EventArgs e)
        //{
        //    SaveFileDialog fileDialog = new SaveFileDialog();
        //    fileDialog.Title = "导出Excel";
        //    fileDialog.Filter = "Excel文件(*.xls)|*.xls";
        //    DialogResult dialogResult = fileDialog.ShowDialog(this);
        //    if (dialogResult == DialogResult.OK)
        //    {
        //        DevExpress.XtraPrinting.XlsExportOptions options = new DevExpress.XtraPrinting.XlsExportOptions();
        //        gridControl2.ExportToXls(fileDialog.FileName);
        //        DevExpress.XtraEditors.XtraMessageBox.Show("保存成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        //    }
        //}

        //private void gridView1_FocusedRowChanged(object sender, DevExpress.XtraGrid.Views.Base.FocusedRowChangedEventArgs e)
        //{
        //    string strSql;
        //    String zjph = gridView1.GetFocusedRowCellValue("品号").ToString();
        //    String gcph = gridView1.GetFocusedRowCellValue("工厂ID").ToString();
        //    strSql = "SELECT R_YJ_ID AS ID,R_ZJ_ID AS ParentID,B.ITEM_NAME 品名,B.ITEM_SPECIFICATION 规格,B.ITEM_CODE 品号,R_Level,[Path] path,0 单位成本 FROM d_Bom1('" + zjph + "','" + gcph + "') Z LEFT JOIN ITEM A ON Z.R_ZJPH=A.ITEM_CODE LEFT JOIN ITEM B ON Z.R_YJPH=B.ITEM_CODE  WHERE R_Level<=" + textBox2.Text + " ORDER BY [Path],R_Level";
            
        //    //textBox1.Text = strSql;
        //    try
        //    {
        //        DataTable dataTable;
        //        dataTable = db.GetDataSet1(strSql, "PURTD").Tables["PURTD"];
        //        gridControl2.DataSource = dataTable;
        //        treeList1.DataSource = dataTable;
        //        treeList1.KeyFieldName = "ID";
        //        treeList1.ParentFieldName = "ParentID";
        //        treeList1.Columns["品号"].Visible = false;
        //        treeList1.Columns["R_Level"].Visible = false;
        //        treeList1.Columns["path"].Visible = false;
        //        treeList1.Columns["单位成本"].Visible = false;
        //        treeList1.Columns["规格"].Visible = false;
        //        //treeList1.ExpandAll();
        //        treeList1.Refresh();
        //    }
        //    catch
        //    {

        //    }
        //}

        //private void 抓取明细单价ToolStripMenuItem_Click(object sender, EventArgs e)
        //{
        //    string strSql;
        //    String zjph = gridView1.GetFocusedRowCellValue("品号").ToString();
        //    String gcph = gridView1.GetFocusedRowCellValue("工厂ID").ToString();
        //    strSql = "SELECT R_YJ_ID AS ID,R_ZJ_ID AS ParentID,B.ITEM_NAME 品名,B.ITEM_SPECIFICATION 规格,B.ITEM_CODE 品号,R_Level,[Path] path,CAST(ROUND((SELECT TOP 1 (K.CE1_CUR_LEVEL+K.CE2_CUR_LEVEL+K.CE3_CUR_LEVEL+K.CE4_CUR_LEVEL+K.CE5_CUR_LEVEL+K.CE6_CUR_LEVEL+K.CE7_CUR_LEVEL+K.CE8_CUR_LEVEL+K.CE9_CUR_LEVEL+K.CE10_CUR_LEVEL+K.CE11_CUR_LEVEL+K.CE12_CUR_LEVEL+K.CE13_CUR_LEVEL+K.CE14_CUR_LEVEL+K.CE15_CUR_LEVEL+K.CE16_CUR_LEVEL+K.CE17_CUR_LEVEL+K.CE18_CUR_LEVEL+CE1_LOW_LEVEL+CE2_LOW_LEVEL+CE3_LOW_LEVEL+CE4_LOW_LEVEL+CE5_LOW_LEVEL+CE6_LOW_LEVEL+CE7_LOW_LEVEL+CE8_LOW_LEVEL+CE9_LOW_LEVEL+CE10_LOW_LEVEL+CE11_LOW_LEVEL+CE12_LOW_LEVEL+CE13_LOW_LEVEL+CE14_LOW_LEVEL+CE15_LOW_LEVEL+CE16_LOW_LEVEL+CE17_LOW_LEVEL+CE18_LOW_LEVEL) FROM SIMULATE_COST_RESULT K WHERE K.ITEM_ID=B.ITEM_BUSINESS_ID AND K.PARENT_ITEM_ID=A.ITEM_BUSINESS_ID AND VERSION_TIMES='01'),2) AS REAL) 单位成本 FROM d_Bom1('" + zjph + "','" + gcph + "') Z LEFT JOIN ITEM A ON Z.R_ZJPH=A.ITEM_CODE LEFT JOIN ITEM B ON Z.R_YJPH=B.ITEM_CODE  WHERE R_Level<=" + textBox2.Text + " ORDER BY [Path],R_Level";

        //    //textBox1.Text = strSql;
        //    try
        //    {
        //        DataTable dataTable;
        //        dataTable = db.GetDataSet1(strSql, "PURTD").Tables["PURTD"];
        //        gridControl2.DataSource = dataTable;
        //        treeList1.DataSource = dataTable;
        //        treeList1.KeyFieldName = "ID";
        //        treeList1.ParentFieldName = "ParentID";
        //        treeList1.Columns["品号"].Visible = false;
        //        treeList1.Columns["R_Level"].Visible = false;
        //        treeList1.Columns["path"].Visible = false;
        //        treeList1.Columns["单位成本"].Visible = false;
        //        treeList1.Columns["规格"].Visible = false;
        //        //treeList1.ExpandAll();
        //        treeList1.Refresh();
        //    }
        //    catch
        //    {

        //    }
        //}

        //private void gridControl1_Click(object sender, EventArgs e)
        //{

        //}
   // }

//}
}
